﻿using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Drawing=System.Drawing;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop;
using System.Drawing;
using System;
using System.Reflection;

namespace SY_Anekay_LMJ_OutPutExcelReport
{
    internal class FillProduceSheetTool
    {
        private Workbook workbook;
        private DataSet data;
        public Workbook Workbook { get { return workbook; } set { workbook = value; } }
        public DataSet Data { get { return data; }set { data = value; } }
        private string flashBefore;
        private string flashAfter;

        private string[] BINEntryTitle = new string[] {"解前1B",
            "解前1P",
            "解前2B",
            "解前2P",
            "解前3",
            "解前4",
            "解前5",
            "解后1B",
            "解后1P",
            "解后2B",
            "解后2P",
            "解后3",
            "解后4",
            "解后5",
            "EF(无ID)",
            "ES",
            "破损"};        

        private string[] BINSubHeadContent = new String[] { "BIN1B",
            "BIN1P",
            "BIN2B",
            "BIN2P",
            "BIN3",
            "BIN4",
            "BIN5"
            };

        private string[] capacityEntryTitle = new string[] {                        
            "有容量前",
            "0M(解前)",
            "有容量后",
            "0M(解后)",
            "EF(无ID)",
            "ES",
            "破损"};

        private string[] capactitySubHeadContent = new String[] { 
            "有容量",
            "OM"            
            };

        private string[] IDEntryTitle=new string[] {
            "解前有ID",
            "解后有ID",
            "EF(无ID)",
            "ES",
            "破损"
        };

        private string[] IDSubHeadContent = new String[] { 
            "有ID"
            };

        private string[] capacityBINEntryTitle = new string[] {"解前1B",
            "解前1P",
            "解前2B",
            "解前2P",
            "解前3",
            "解前4",
            "解前5",
            "解后1B",
            "解后1P",
            "解后2B",
            "解后2P",
            "解后3",
            "解后4",
            "解后5",
            "EF(无ID)",
            "ES",
            "破损"};

        private string[] capactityBINSubHeadContent = new String[] {  "BIN1B",
            "BIN1P",
            "BIN2B",
            "BIN2P",
            "BIN3",
            "BIN4",
            "BIN5"
            };

        private Dictionary<string, string> translateCHNToEng = new Dictionary<string, string> {
                {"型号","Model"},
                {"单号","WorkOrder"},
                {"原始单号","OriginBillNo"},
                {"测试方案","TestPlan"},
                {"光罩号码","MaskNumber"},
                {"整单良率","EmptyGoodRate"},
                {"Flash型号解前","FlashBefore"},
                {"Flash型号解后","FlashAfter"},
                {"ID解前","IdBefore"},
                {"ID解后","IdAfter"},
                {"来料数量","IncomingQTY"},
                {"下粹数量","DishesQTY"},
                {"已测试数量","EmptyTestedQty"},
                {"未测试数量","EmptyTestingQty"},
                {"参数设置","ParameterSetting"}
            };

        //BIN与解前有容量分BIN都是用此表头
        private string[] summaryEntryHeadBIN = new string[] 
        {
            "型号",  "厚度",  "已测数量",    "解前良品",    "解前良率",    "解后良品",    "解后良率",    "总良率", "备注"

        };
        private string[] summaryEntryHeadCapacity = new string[]
        {
            "型号",  "厚度",  "已测数量",    "解前有容量",    "解前容量良率",    "解后有容量",    "解后容量良率",    "总良率", "备注"   
        };
        private string[] summaryEntryHeadID = new string[]
       {
            "型号",  "厚度",  "已测数量",    "解前ID",    "解前ID良率",    "解后ID",    "解后ID良率",    "总良率", "备注"
       };






        public FillProduceSheetTool(Workbook workbook, DataSet dataSet,string flashBefore="",string flashAfter="")
        {
            this.workbook = workbook;
            this.data = dataSet;
            this.flashBefore = flashBefore;
            this.flashAfter = flashAfter;
        }

        public void FillExamineSheet(ExamineType type,int sheetIndex)
        {
            List<Cell> headKeyCell = new List<Cell>();
            List<Cell> headValueCell = new List<Cell>();
            List<Cell> subHeadCell = new List<Cell>();
            List<Cell> entryTitleCell = new List<Cell>();
            List<Cell> entryContentCell = new List<Cell>();
            List<Cell> entrySubSumCell = new List<Cell>();
            List<Cell> entrySubSumPercentCell = new List<Cell>();
            List<Cell> entrySumCell = new List<Cell>();
            List<Cell> entrySumPercentCell = new List<Cell>();
            Cell titleCell = new Cell(3, 1, "测试明细表", true) { FontSize = 20, IsMerge = true, X2 = 10, FontColor = Color.CornflowerBlue };
            Worksheet worksheet = new Worksheet();

            ExamineType examineType = type;
            string headDataTableName = "";
            string subHeadTableName = "";
            string entryDataTableName = "";
            char entryStratXPoint = new char();
            char entryEndXPoint = new char();
            List<String> materialClass = new List<string>();

            
            
            switch (type)
            {
                case ExamineType.BIN:
                    {
                        
                        worksheet = (Worksheet)workbook.Worksheets.Add(Missing.Value,workbook.Worksheets[sheetIndex-1],1);
                        //worksheet= workbook.Worksheets.Add();
                        worksheet.Name = "直接测分BIN";
                        headDataTableName = "BINHeadData";
                        subHeadTableName = "BINSubTitle";
                        entryDataTableName = "BINEntryData";
                        entryStratXPoint = Convert.ToChar("C");
                        entryEndXPoint = Convert.ToChar("T");
                        materialClass= BINEntryTitle.ToList();
                    }
                    break;
                case ExamineType.ID:
                    {
                        worksheet = (Worksheet)workbook.Worksheets.Add(Missing.Value, workbook.Worksheets[sheetIndex - 1], 1);
                        worksheet.Name = "测ID";
                        headDataTableName = "BINHeadData";
                        subHeadTableName = "BINSubTitle";
                        entryDataTableName = "BINEntryData";
                        entryStratXPoint = Convert.ToChar("C");                        
                        materialClass = IDEntryTitle.ToList();
                        entryEndXPoint = Convert.ToChar(Convert.ToInt16(entryStratXPoint) + materialClass.Count);
                    }
                    break;
                case ExamineType.Capacity:
                    {
                        worksheet = (Worksheet)workbook.Worksheets.Add(Missing.Value, workbook.Worksheets[sheetIndex - 1], 1);
                        worksheet.Name = "测容量";
                        headDataTableName = "BINHeadData";
                        subHeadTableName = "BINSubTitle";
                        entryDataTableName = "BINEntryData";
                        entryStratXPoint = Convert.ToChar("C");                        
                        materialClass = capacityEntryTitle.ToList();
                        entryEndXPoint = Convert.ToChar(Convert.ToInt16(entryStratXPoint) + materialClass.Count);
                    }
                    break;
                case ExamineType.CapacitytBIN:
                    {
                        worksheet = (Worksheet)workbook.Worksheets.Add(Missing.Value, workbook.Worksheets[sheetIndex - 1], 1);
                        worksheet.Name = "解前有容量分BIN";
                        headDataTableName = "BINHeadData";
                        subHeadTableName = "BINSubTitle";
                        entryDataTableName = "BINEntryData";
                        entryStratXPoint = Convert.ToChar("C");                        
                        materialClass = capacityBINEntryTitle.ToList();
                        entryEndXPoint = Convert.ToChar(Convert.ToInt16(entryStratXPoint) + materialClass.Count);
                    }
                    break;
                default: throw new Exception("不存在的测试类型");
            }

            FillHeadCell(headKeyCell, headValueCell, data, headDataTableName,examineType, flashBefore, flashAfter,out Cell totalExaminedCell,out Cell qualifiedRateCell);
            FillSubHeadCell(subHeadCell, data, headDataTableName, subHeadTableName,examineType,out List<int> qualityColumns);
            FillEntryCell(entryTitleCell, entryContentCell, entrySubSumCell, entrySubSumPercentCell, entrySumCell, entrySumPercentCell, data, entryDataTableName,entryStratXPoint,entryEndXPoint,materialClass,examineType, ref totalExaminedCell,ref qualifiedRateCell,qualityColumns);


            if (headKeyCell.Count != 0 & headValueCell.Count != 0)
            {
                Range headFrame = worksheet.Range[worksheet.Cells[headKeyCell.Min(p => p.Y) - 1, headKeyCell.Min(p => p.X) - 1],
                   worksheet.Cells[headValueCell.Max(p => p.Y) + 1, headValueCell.Max(p => p.X) + 1]];
                headFrame.Interior.Color = Drawing.Color.LightSteelBlue;
                headFrame.Borders[XlBordersIndex.xlEdgeTop].Color = Color.RoyalBlue;
                headFrame.Borders[XlBordersIndex.xlEdgeBottom].Color = Color.RoyalBlue;
                headFrame.Borders[XlBordersIndex.xlEdgeLeft].Color = Color.RoyalBlue;
                headFrame.Borders[XlBordersIndex.xlEdgeRight].Color = Color.RoyalBlue;
            }

            List<Cell> cellGatherCollection = new List<Cell>();
            cellGatherCollection.AddRange(headKeyCell);
            cellGatherCollection.AddRange(headValueCell);
            cellGatherCollection.AddRange(subHeadCell);
            cellGatherCollection.AddRange(entryContentCell);
            cellGatherCollection.AddRange(entryTitleCell);
            cellGatherCollection.AddRange(entrySubSumCell);
            cellGatherCollection.AddRange(entrySubSumPercentCell);
            cellGatherCollection.AddRange(entrySumCell);
            cellGatherCollection.AddRange(entrySumPercentCell);
            cellGatherCollection.Add(titleCell);

            PutCellIntoWorksheet(cellGatherCollection, worksheet);
        }

        private void FillHeadCell(List<Cell> headKeyCell, List<Cell> headValueCell,DataSet dataSet,string headDataTableName,ExamineType examineType,string flashBefore,string flashAfter,
            out Cell totalExamined,out Cell qualifiedRate)
        {
            Cell totalExaminedCell = new Cell(1,1);
            Cell qualifiedCell = new Cell(1, 1);
            if (examineType == ExamineType.CapacitytBIN)
                headKeyCell.AddRange(new List<Cell>{
                new Cell(1, 1, "型号"),
                new Cell(1, 2, "单号"),
                new Cell(1, 3, "原始单号"),
                new Cell(1, 4, "测试方案"),
                new Cell(3, 1, "光罩号码"),
                new Cell(3, 2, "整单良率"),
                new Cell(3, 3, "Flash型号解前"),
                new Cell(3, 4, "Flash型号解后"),
                new Cell(3, 5, "ID解前"),
                new Cell(3, 6, "ID解后"),                
                new Cell(5, 1, " "),
                new Cell(5, 2, "解前有容量数量"),
                new Cell(5, 3, "已测试数量"),
                new Cell(5, 4, "未测试数量"),
                new Cell(5, 5, "参数设置"),
            });
            else
                headKeyCell.AddRange(new List<Cell>{
                new Cell(1, 1, "型号"),
                new Cell(1, 2, "单号"),
                new Cell(1, 3, "原始单号"),
                new Cell(1, 4, "测试方案"),
                new Cell(3, 1, "光罩号码"),
                new Cell(3, 2, "整单良率"),
                new Cell(3, 3, "Flash型号解前"),
                new Cell(3, 4, "Flash型号解后"),
                new Cell(3, 5, "ID解前"),
                new Cell(3, 5, "ID解后"),
                new Cell(5, 1, "来料数量"),
                new Cell(5, 2, "下粹数量"),
                new Cell(5, 3, "已测试数量"),
                new Cell(5, 4, "未测试数量"),
                new Cell(5, 5, "参数设置"),
            });

            foreach (var cell in headKeyCell)
            {
                cell.Y += 2;
                cell.X += 3;
                cell.IsBold = true;
                cell.BackColor = Color.CornflowerBlue;
                cell.FontColor = Color.White;
                cell.BorderColor = Color.LightSteelBlue;
                cell.Width = 20M;

            }

            var rows = dataSet.Tables?[headDataTableName]?.Rows;

            
            DataRow rowHeadValue = rows.Count==0?null:rows[0];
            if (rowHeadValue != null)
            {
                foreach (var cell in headKeyCell)
                {
                    if (cell.Content == "解前有容量数量")
                        headValueCell.Add(new Cell(cell.X + 1, cell.Y, Convert.ToString(data.Tables["CapacityQty"].Rows[0]["QTY"])) { BorderColor = Color.LightSteelBlue, Width = 20 });
                    else if (string.IsNullOrEmpty(cell.Content.Trim()))
                        headValueCell.Add(new Cell(cell.X + 1, cell.Y, "") { BorderColor = Color.LightSteelBlue, Width = 20 });
                    else if (cell.Content == "已测试数量")
                        headValueCell.Add(totalExaminedCell = new Cell(cell.X + 1, cell.Y) { BorderColor = Color.LightSteelBlue, Width = 20 });
                    else if (cell.Content == "未测试数量")
                        headValueCell.Add(new Cell(cell.X + 1, cell.Y, $"={ Convert.ToChar(cell.X + 64 + 1) }{cell.Y - 2}-{Convert.ToChar(cell.X + 64 + 1) }{cell.Y - 1}") { BorderColor = Color.LightSteelBlue, Width = 20 });
                    else if (cell.Content == "整单良率")
                        headValueCell.Add(qualifiedCell = new Cell(cell.X + 1, cell.Y) { BorderColor = Color.LightSteelBlue, Width = 20, NumberFormat = "0.000%" });
                    else if (cell.Content == "Flash型号解前")
                        headValueCell.Add(new Cell(cell.X + 1, cell.Y, flashBefore) { BorderColor = Color.LightSteelBlue, Width = 20 });
                    else if (cell.Content == "Flash型号解后")
                        headValueCell.Add(new Cell(cell.X + 1, cell.Y, flashAfter) { BorderColor = Color.LightSteelBlue, Width = 20 });
                    else
                        headValueCell.Add(new Cell(cell.X + 1, cell.Y, rowHeadValue[translateCHNToEng[cell.Content]].ToString()) { BorderColor = Color.LightSteelBlue, Width = 20 });
                }
            }
            totalExamined = totalExaminedCell;
            qualifiedRate = qualifiedCell;
        }

        private void FillSubHeadCell(List<Cell> subHeadCell,DataSet dataSet,string headDataTableName,string subHeadDataTableName,ExamineType examineType,out List<int> qualifiedColumns)
        {
            List<int> qualified = new List<int>();
            var dataRows = data.Tables?["BINHeadData"]?.Rows;
            var titleRows = data.Tables?["BINSubTitle"]?.Rows;
            DataRow rowHeadValue = dataRows.Count== 0 ? null : dataRows[0];
            DataRow subHeadValue = titleRows.Count== 0 ? null : titleRows[0];
            int subHeadStartX = 3;
            int subHeadStartY = 11;
            int currentX = subHeadStartX;
            int currentY = subHeadStartY;
            string[] subHeadContent=null;
            switch (examineType)
            {
                case ExamineType.BIN:subHeadContent = BINSubHeadContent;break;
                case ExamineType.Capacity:subHeadContent = capactitySubHeadContent;break;
                case ExamineType.ID:subHeadContent = IDSubHeadContent;break;
                case ExamineType.CapacitytBIN:subHeadContent = capactityBINSubHeadContent;break;
            }


            if (rowHeadValue!=null & subHeadValue!=null)
            {
                subHeadCell.Add(new Cell(currentX, currentY, rowHeadValue["TestPlan"] + " 解码前"));
                currentY += 1;
                subHeadCell.Add(new Cell(currentX, currentY, rowHeadValue["FlashBefore"].ToString()));
                currentY += 1;
                
                foreach (var item in subHeadContent)
                {
                    subHeadCell.Add(new Cell(currentX, currentY, item));
                    if (examineType == ExamineType.BIN | examineType == ExamineType.CapacitytBIN)
                    {
                        subHeadCell.Add(new Cell(currentX, currentY + 1, Convert.ToString(subHeadValue[item])));
                        if (Convert.ToString(subHeadValue[item]).ToUpper() != "OM" && Convert.ToString(subHeadValue[item]).ToUpper() != "0M")
                            qualified.Add(currentX);
                    }
                    else if (examineType == ExamineType.Capacity)
                    {
                        if (item == "有容量")
                            qualified.Add(currentX);
                    }
                    else if (examineType == ExamineType.ID)
                        qualified.Add(currentX);
                    currentX += 1;

                }
                currentY = subHeadStartY;
                subHeadCell.Add(new Cell(currentX, currentY, rowHeadValue["TestPlan"] + " 解码后"));
                currentY += 1;
                subHeadCell.Add(new Cell(currentX, currentY, rowHeadValue["FlashAfter"].ToString()));
                currentY += 1;
                foreach (var item in subHeadContent)
                {
                    subHeadCell.Add(new Cell(currentX, currentY, item));
                    if (examineType == ExamineType.BIN | examineType == ExamineType.CapacitytBIN)
                    {
                        subHeadCell.Add(new Cell(currentX, currentY + 1, Convert.ToString(subHeadValue[item])));
                        if (Convert.ToString(subHeadValue[item]).ToUpper() != "OM" && Convert.ToString(subHeadValue[item]).ToUpper() != "0M")
                            qualified.Add(currentX);
                    }
                    else if (examineType == ExamineType.Capacity)
                    {
                        if (item == "有容量")
                            qualified.Add(currentX);
                    }
                    else if (examineType == ExamineType.ID)
                        qualified.Add(currentX);                    
                    currentX += 1;
                }
                subHeadCell.AddRange(new List<Cell> {
                            new Cell(currentX , currentY , "EF(无ID)"),
                new Cell(currentX +1, currentY , "ES(解码NG)"),
                new Cell(currentX +2, currentY , "破损"),
                new Cell(currentX +3, currentY , "TOTAL"),
            });

                


                foreach (var cell in subHeadCell)
                {
                    cell.FontColor = Color.White;
                    cell.BackColor = Color.CornflowerBlue;
                }
            }
            qualifiedColumns = qualified;


        }

        private void FillEntryCell(List<Cell> entryTitleCell, List<Cell> entryContentCell, List<Cell> entrySubSumCell, List<Cell> entrySubSumPercentCell, List<Cell> entrySumCell, List<Cell> entrySumPercentCell,DataSet dataSet,string entryDataTableName,char startPoint,char endPoint,List<string> materialClass,ExamineType examineType,ref Cell totalExamined,ref Cell qualified, List<int> qualifiedColunmn)
        {
            int entryDataX = 1;
            int entryDataY = 16;
            System.Data.DataTable entryDataTable = dataSet.Tables[entryDataTableName];
            List<BINEntryElement> binElement = new List<BINEntryElement>();

            foreach (System.Data.DataRow row in entryDataTable.Rows)
            {
                if((ExamineType)Convert.ToInt16( row["ExamineType"])==examineType)
                    binElement.Add(new BINEntryElement(Convert.ToDecimal(row["Thickness"]), Convert.ToDecimal(row["QTY"]), row["MaterialClass"].ToString(), row["Group"].ToString(), Convert.ToDateTime(row["DateTime"]), row["ExamineType"].ToString()));
            }

            var entryGroup = from BINEntryElement t1 in binElement
                             //where t1.ExamineType == "1"
                             orderby t1.Thickness, t1.Date, t1.Group
                             group new { t1.Thickness, t1.Date, t1.Group, } by new { t1.Thickness, t1.Date, t1.Group } into t2
                             select new { t2.Key.Thickness, t2.Key.Date, t2.Key.Group };

            //EntryGroupingInformation,Set thickness whlie thickness value change
            decimal thicknessTemp = 0;

            string group = "";
            DateTime date = DateTime.MinValue;
            bool isFirstRow = true;
            int rowIndexOfSumStart = 0;
            int rowIndexOfSumEnd = 0;

            foreach (var item in entryGroup)
            {

                entryDataX = 1;

                if (item.Thickness != thicknessTemp)
                {
                    rowIndexOfSumStart = isFirstRow ? entryDataY + 1 : rowIndexOfSumStart;
                    if (!isFirstRow)
                    {
                        entrySubSumCell.AddRange(GetSubSumCell(ref entryTitleCell, entryDataY, rowIndexOfSumStart, rowIndexOfSumEnd,startPoint, endPoint));
                        entryDataY += 1;
                        entrySubSumPercentCell.AddRange(GetPercentCell(ref entryTitleCell, entryDataY, startPoint, endPoint));
                        entryDataY += 1;
                        rowIndexOfSumStart = entryDataY + 1;
                    }


                    thicknessTemp = item.Thickness;
                    entryTitleCell.Add(new Cell(entryDataX, entryDataY, Math.Round(thicknessTemp, 4).ToString() + "mm", true));
                    entryDataY += 1;
                    isFirstRow = false;
                }

                entryTitleCell.Add(new Cell(entryDataX, entryDataY, item.Date.ToShortDateString().ToString()));
                date = item.Date;
                entryDataX += 1;
                entryTitleCell.Add(new Cell(entryDataX, entryDataY, item.Group));
                entryDataX += 1;
                group = item.Group;
                foreach (var type in materialClass)
                {
                    entryContentCell.Add(new Cell(entryDataX, entryDataY, Convert.ToDecimal(binElement.Where(p => p.Thickness == thicknessTemp && p.Date == date && p.Group == group && p.MaterialClass == type).Select(q => q.Qty)?.FirstOrDefault()).ToString("0.##")));
                    entryDataX += 1;
                }

                
                entryContentCell.Add(new Cell(entryDataX, entryDataY, $"=SUM({startPoint}{entryDataY}:{Convert.ToChar( endPoint-1)}{entryDataY})"));
                entryDataY += 1;
                rowIndexOfSumEnd = entryDataY - 1;
            }

            List<Cell> lastSubSumRow = GetSubSumCell(ref entryTitleCell, entryDataY, rowIndexOfSumStart, rowIndexOfSumStart, startPoint, endPoint);
            entrySubSumCell.AddRange(lastSubSumRow);
            entryDataY += 1;
            List<Cell> lastSubSumPercentRow = GetPercentCell(ref entryTitleCell, entryDataY, startPoint, endPoint);
            entryDataY += 1;
            entrySubSumPercentCell.AddRange(lastSubSumPercentRow);

            entryTitleCell.Add(new Cell(1, entryDataY, "合计", true));
            entryDataY += 1;
            var sumCell = GetSumCell(ref entryTitleCell, entryDataY, entryContentCell.Select(p => p.Y).Distinct(), startPoint, endPoint);
            entrySumCell.AddRange(sumCell);
            totalExamined.Content = sumCell.Where(p => p.X == sumCell.Max(q => q.X)).Select(p => p.Content).FirstOrDefault();
            entryDataY += 1;
            entrySumPercentCell.AddRange(GetPercentCell(ref entryTitleCell, entryDataY, startPoint, endPoint));

            StringBuilder builder = new StringBuilder("=SUM(");
            foreach (var item in qualifiedColunmn)
            {
                builder.Append(Convert.ToChar(item + 64).ToString() + entryDataY.ToString()+",");
            }
            builder.Remove(builder.Length-1, 1);
            qualified.Content = builder.ToString();
        }

        public List<Cell> GetSubSumCell(ref List<Cell> entryTitleCell, int yCoordinate, int rowIndexOfSumStart, int rowIndexOfSumEnd, Char startXcoordinate, Char endXCoordinate)
        {
            List<Cell> sumCellList = new List<Cell>();
            
            if (char.IsLetter(startXcoordinate) & char.IsLetter(endXCoordinate))
            {
                entryTitleCell.Add(new Cell(1, yCoordinate, "Q'ty"));
                Char start = startXcoordinate;
                Char end = endXCoordinate;
                if(rowIndexOfSumStart!=0&&rowIndexOfSumEnd !=0)
                    for (int i = Convert.ToInt32(start); i <= Convert.ToInt32(end); i++)
                    {
                        char letter = Convert.ToChar(i);
                        sumCellList.Add(new Cell(i - 64, yCoordinate, $"=SUM({letter.ToString().ToUpper()}{rowIndexOfSumStart.ToString()}:{letter.ToString().ToUpper()}{rowIndexOfSumEnd.ToString()}"));
                    }

                return sumCellList;
            }
            else
                throw new Exception($"Incorrect parameter startXcoordinate:[{startXcoordinate}] or endXCoordinate:[{endXCoordinate}],require letter");
        }

        /// <summary>
        /// Common use for summary and subsummary
        /// </summary>
        /// <param name="entryTitleCell"></param>
        /// <param name="yCoordinate"></param>
        /// <param name="startXcoordinate"></param>
        /// <param name="endXCoordinate"></param>
        /// <returns></returns>
        public List<Cell> GetPercentCell(ref List<Cell> entryTitleCell, int yCoordinate, Char startXcoordinate, Char endXCoordinate)
        {
            List<Cell> sumPercentCell = new List<Cell>();
            if (char.IsLetter(startXcoordinate) & char.IsLetter(endXCoordinate))
            {
                entryTitleCell.Add(new Cell(1, yCoordinate, "Q'ty%"));
                Char start = startXcoordinate;
                Char end = endXCoordinate;
                for (int i = Convert.ToInt32(start); i <= Convert.ToInt32(end); i++)
                {
                    Char column = Convert.ToChar(i);
                    sumPercentCell.Add(new Cell(i - 64, yCoordinate, $"={column.ToString().ToUpper()}{yCoordinate - 1}/{end}{yCoordinate - 1}") { NumberFormat = "0.000%" });
                }
                return sumPercentCell;
            }
            else
                throw new Exception($"Incorrect parameter startXcoordinate:[{startXcoordinate}] or endXCoordinate:[{endXCoordinate}],require letter");

        }

        public List<Cell> GetSumCell(ref List<Cell> entryTitleCell, int yCoordinate, IEnumerable<int> entryContentYCoordinateCollection, Char startXcoordinate, Char endXCoordinate)
        {
            List<Cell> SumCell = new List<Cell>();
            if (char.IsLetter(startXcoordinate) & char.IsLetter(endXCoordinate))
            {
                entryTitleCell.Add(new Cell(1, yCoordinate, "Q'ty"));
                var entryCellYPoint = entryContentYCoordinateCollection.Distinct();
                Char start = startXcoordinate;
                Char end = endXCoordinate;
                for (int i = Convert.ToInt32(start); i <= Convert.ToInt32(end); i++)
                {
                    char column = Convert.ToChar(i);
                    StringBuilder elementPlusString = new StringBuilder("=");
                    foreach (var YPoint in entryCellYPoint)
                    {
                        elementPlusString.Append(column.ToString().ToUpper() + YPoint.ToString() + "+");
                    }
                    elementPlusString.Remove(elementPlusString.Length - 1, 1);
                    SumCell.Add(new Cell(i - 64, yCoordinate, $"{elementPlusString}"));
                }
                return SumCell;
            }
            else
                throw new Exception($"Incorrect parameter startXcoordinate:[{startXcoordinate}] or endXCoordinate:[{endXCoordinate}],require letter");
        }

        public void FillSummarySheet(int sheetIndex,List<ExamineType> examineTypes)
        {
            Worksheet worksheet = new Worksheet();
            worksheet = (Worksheet)workbook.Worksheets.Add(Missing.Value, workbook.Worksheets[sheetIndex - 1], 1);
            worksheet.Name = "测试数据汇总";
            Cell title = new Cell(1, 1, "测试数据汇总", true) { FontSize = 20, IsMerge = true,X2=9,FontColor=Color.CornflowerBlue };
            List<Cell> EntryHeadCell = new List<Cell>();
            List<Cell> EntryValueCell = new List<Cell>();
            System.Data.DataTable dt = Data.Tables["BINEntryData"];
            System.Data.DataTable dtEntry = data.Tables["SummaryEntry"];
            string[] summaryEntryHead;

            int headStartX = 1;
            int headStartY = 2;
            int headX = headStartX;
            int headY = headStartY;
            int entryStartX = 1;
            int entryStartY = 3;
            int entryX = entryStartX;
            int entryY = entryStartY;

            //Every circle is one of examine types
            foreach (var examineType in examineTypes)
            {
                headY = headY == headStartY ? headY : headY + 1;
                switch (examineType)
                {
                    case ExamineType.BIN: summaryEntryHead = summaryEntryHeadBIN; break;
                    case ExamineType.CapacitytBIN: summaryEntryHead = summaryEntryHeadBIN; break;
                    case ExamineType.Capacity:summaryEntryHead = summaryEntryHeadCapacity;break;
                    case ExamineType.ID:summaryEntryHead = summaryEntryHeadID;break;
                        default:summaryEntryHead = new string[] { "empty" };break;
                }
                if (summaryEntryHead.FirstOrDefault() == "empty")
                    throw new Exception($"生成汇总部分时输入的测试类型无效，测试类型为{examineType.ToString()}");

                foreach (var headtitle in summaryEntryHead)
                {
                    EntryHeadCell.Add(new Cell(headX, headY, headtitle,true) { BackColor = Color.CornflowerBlue, FontColor = Color.White });
                    headX += 1;
                }
                var examineHaft = dt.AsEnumerable().GroupBy(p =>
                       new { type = p.Field<string>("ExamineType"), model = p.Field<string>("Model"), thickness = p.Field<decimal>("Thickness") })
                       .Select(p => new { key = p.Key, examined = p.Sum(q => q.Field<decimal>("QTY")) })
                       .Where(p=>(ExamineType)Convert.ToInt16( p.key.type)==examineType);
                var examineHaftLeft = dtEntry.AsEnumerable().Where(p =>  (ExamineType)Convert.ToInt16(p.Field<string>("ExamineType")) == examineType&&Convert.ToBoolean(p.Field<bool>("IsQualified")))
                    .Select(p => new { thickness = p.Field<decimal>("Thickness"), model = p.Field<string>("Model"),code=p.Field<string>("Code"), qty = p.Field<decimal>("QTY") });

                entryY = entryY == entryStartY ? entryY : headY + 1;
                //every circle is one of entry data
                foreach (var value in examineHaft)
                {
                    EntryValueCell.Add(new Cell(entryX, entryY, value.key.model));
                    entryX += 1;
                    EntryValueCell.Add(new Cell(entryX, entryY, value.key.thickness.ToString()));
                    entryX += 1;
                    EntryValueCell.Add(new Cell(entryX, entryY, value.examined.ToString()));
                    entryX += 1;
                    EntryValueCell.Add(new Cell(entryX, entryY, examineHaftLeft.Where(p=>p.thickness==value.key.thickness&&p.model==value.key.model&&p.code=="Before")
                        .Select(p=>p.qty).FirstOrDefault().ToString()));
                    entryX += 1;
                    EntryValueCell.Add(new Cell(entryX, entryY, $"=D{entryY}/C{entryY}") { NumberFormat = "0.000%" });
                    entryX += 1;
                    EntryValueCell.Add(new Cell(entryX, entryY, examineHaftLeft.Where(p => p.thickness == value.key.thickness && p.model == value.key.model && p.code == "After")
                        .Select(p => p.qty).FirstOrDefault().ToString()));
                    entryX += 1;
                    EntryValueCell.Add(new Cell(entryX, entryY, $"=F{entryY}/C{entryY}") { NumberFormat = "0.000%" });
                    entryX += 1;
                    EntryValueCell.Add(new Cell(entryX, entryY, $"=E{entryY}+G{entryY}") { NumberFormat = "0.000%" });


                    entryX = entryStartX;
                    entryY += 1;
                }
                headStartY = entryY + 1;
                headX = headStartX;
                headY = entryY;

            }
            var cellGatherCollection = new List<Cell>(EntryHeadCell);
            cellGatherCollection.Add(title);
            cellGatherCollection.AddRange(EntryValueCell);

            PutCellIntoWorksheet(cellGatherCollection, worksheet);




            //var z= dt.AsEnumerable().GroupBy(p=>new { type= p.Field<string>("ExamineType"), model= p.Field<string>("Model"),thickness=p.Field<decimal>("Thickness",)})

        }

        public static void PutCellIntoWorksheet(List<Cell> cellGather,Worksheet worksheet)
        {
            foreach (var cell in cellGather)
            {
                worksheet.Cells[cell.Y, cell.X] = cell.Content;
                Range range = cell.IsMerge ? worksheet.Range[worksheet.Cells[cell.Y, cell.X], worksheet.Cells[cell.Y2==0?cell.Y:cell.Y2, cell.X2==0?cell.X:cell.X2]] :
                    worksheet.Range[worksheet.Cells[cell.Y, cell.X], worksheet.Cells[cell.Y, cell.X]];
                if (cell.IsMerge)
                    range.Merge(Missing.Value);
                range.Interior.Color = cell.BackColor;
                range.Font.Size = cell.FontSize;
                range.Font.Color = cell.FontColor;
                range.Font.Bold = cell.IsBold;
                range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                range.Borders.Color = cell.BorderColor;
                range.ColumnWidth = cell.Width == 0 ? range.ColumnWidth : cell.Width;
                range.NumberFormat = cell.NumberFormat ?? range.NumberFormat;
                range.WrapText = true;
            }
            
        }
    }
}


public enum ExamineType
{
    Empty=0,
    BIN=1,
    CapacitytBIN = 2,
    Capacity =3,
    ID=4,
    Other=5,
}

public class ExamineTypeCompare : IComparer<ExamineType>
{
    public int Compare(ExamineType x, ExamineType y)
    {
        if (x == ExamineType.CapacitytBIN)
            return 1;

        else if (y == ExamineType.CapacitytBIN)
            return -1;
        else
            return x.CompareTo(y);
    }
}




